const express = require("express");
const router = express.Router();
const path = require("path");
const pool = require("../pool.js");
const poolmomo = require("../poolmomo.js");
const utils = require("../utils/index.js");
const utilsServer = require("../utils/serverInfo.js");
const moment = require("moment-timezone");
const multer = require("multer");
const AdmZip = require("adm-zip");
const AppInfoParser = require("app-info-parser");
const plist = require("plist");
const fs = require("fs");
const { readManifest } = require("node-aab-parser");
const msgUtils = require("../utils/feishu.js");
const crypto = require("crypto");
const CryptoJS = require("crypto-js");
const FormData = require("form-data");
const { exec } = require("child_process");
const config = require("../config");
const axios = require("axios");
const jwt = require("jsonwebtoken");
const jwksClient = require('jwks-rsa');
const xlsx = require('xlsx');
// MySQL查询封装函数
async function mysqlQuery(sql, arr = []) {
    return new Promise((resolve, reject) => {
        pool.query(sql, arr, (error, results, fields) => {
            if (error) {
                return reject(error);
            }
            resolve(results);
        });
    });
}
async function mysqlQueryMOMO(sql, arr = []) {
    return new Promise((resolve, reject) => {
        poolmomo.query(sql, arr, (error, results, fields) => {
            if (error) {
                return reject(error);
            }
            resolve(results);
        });
    });
}

/**
 * 获取统计数据列表（POST）
 */
router.post('/stats', async (req, res) => {
    try {
        const {
            pk,
            product_id,
            start_date,
            end_date,
            page = 1,
            pageSize = 20
        } = req.body;

        let whereConditions = [];
        let params = [];

        if (pk) {
            whereConditions.push('pk = ?');
            params.push(pk);
        }

        if (product_id) {
            whereConditions.push('product_id = ?');
            params.push(product_id);
        }

        if (start_date) {
            whereConditions.push('stat_date >= ?');
            params.push(start_date);
        }

        if (end_date) {
            whereConditions.push('stat_date <= ?');
            params.push(end_date);
        }

        const whereClause = whereConditions.length > 0
            ? 'WHERE ' + whereConditions.join(' AND ')
            : '';

        // 获取总数
        const countSql = `
            SELECT COUNT(*) as total
            FROM js_app_subscription_daily_stats
            ${whereClause}
        `;
        const countResult = await mysqlQuery(countSql, params);
        const total = countResult[0].total;

        // 获取分页数据
        const offset = (page - 1) * pageSize;
        const dataSql = `
            SELECT *
            FROM js_app_subscription_daily_stats
            ${whereClause}
            ORDER BY stat_date DESC, pk, product_id
            LIMIT ? OFFSET ?
        `;
        const dataResult = await mysqlQuery(dataSql, [...params, parseInt(pageSize), offset]);

        res.json({
            success: true,
            data: {
                list: dataResult,
                pagination: {
                    page: parseInt(page),
                    pageSize: parseInt(pageSize),
                    total: total,
                    totalPages: Math.ceil(total / pageSize)
                }
            }
        });

    } catch (error) {
        console.error('获取统计数据失败:', error);
        res.status(500).json({
            success: false,
            message: '获取统计数据失败',
            error: error.message
        });
    }
});

/**
 * 获取产品列表（POST）
 */
router.post('/products', async (req, res) => {
        let { name } = await utils.getUserInfo(req, res);
    
    try {
        const { pk } = req.body;
        
        // 公共where条件数组
        let whereConditions = [
            "environment = 'Production'",
            "product_id IS NOT NULL"
        ];
        let params = [];

        if (pk) {
            whereConditions.unshift('pk = ?');
            params.push(pk);
        }
        const whereClause = whereConditions.length > 0
            ? 'WHERE ' + whereConditions.join(' AND ')
            : '';

        const sql = `
            SELECT DISTINCT pk, product_id
            FROM js_app_apple_notifications
            ${whereClause}
            ORDER BY pk, product_id
        `;

        // 并发查两个数据库
        const [mainResult, momoResult] = await Promise.all([
            mysqlQuery(sql, params),
            mysqlQueryMOMO(sql, params)
        ]);

        // 合并并去重
        const merged = [...mainResult, ...momoResult];
        // 去重逻辑：同pk+product_id的记录只保留一条
        const uniqueMap = new Map();
        merged.forEach(item => {
            const key = `${item.pk}||${item.product_id}`;
            if (!uniqueMap.has(key)) {
                uniqueMap.set(key, item);
            }
        });
        const uniqueList = Array.from(uniqueMap.values());
        let resultList = uniqueList;

        // 判断用户名，并只保留指定 pk
        if (name === 'zj_xiewenjing') {
            const allowedPks = [
                'com.seamobitech.UniversalTVRemoteControl',
                'com.zhijian.remoteControl',
                'com.seamobitech.roku'
            ];
            resultList = uniqueList.filter(item => allowedPks.includes(item.pk));
        }

        res.json({
            success: true,
            data: resultList
        });

    } catch (error) {
        console.error('获取产品列表失败:', error);
        res.status(500).json({
            success: false,
            message: '获取产品列表失败',
            error: error.message
        });
    }
});



/**
 * 获取统计概览数据（POST）
 */
router.post('/overview', async (req, res) => {
    try {
        const { pk, product_id, start_date, end_date } = req.body;

        let whereConditions = [];
        let params = [];

        if (pk) {
            whereConditions.push('pk = ?');
            params.push(pk);
        }

        if (product_id) {
            whereConditions.push('product_id = ?');
            params.push(product_id);
        }

        if (start_date) {
            whereConditions.push('stat_date >= ?');
            params.push(start_date);
        }

        if (end_date) {
            whereConditions.push('stat_date <= ?');
            params.push(end_date);
        }

        const whereClause = whereConditions.length > 0
            ? 'WHERE ' + whereConditions.join(' AND ')
            : '';

        // 获取汇总数据
        const summarySql = `
            SELECT 
                SUM(successful_renewals) as total_renewals,
                SUM(successful_refunds) as total_refunds,
                SUM(trial_users) as total_trial_users,
                SUM(direct_purchase_users) as total_direct_purchase,
                SUM(one_time_purchase_users) as total_one_time_purchase,
                SUM(trial_to_paid_count) as total_conversions,
                AVG(trial_conversion_rate) as avg_conversion_rate
            FROM js_app_subscription_daily_stats
            ${whereClause}
        `;
        const summaryResult = await mysqlQuery(summarySql, params);

        // 获取按日期分组的趋势数据
        const trendSql = `
            SELECT 
                stat_date,
                SUM(successful_renewals) as daily_renewals,
                SUM(successful_refunds) as daily_refunds,
                SUM(trial_users) as daily_trial_users,
                SUM(direct_purchase_users) as daily_direct_purchase,
                SUM(one_time_purchase_users) as daily_one_time_purchase,
                SUM(trial_to_paid_count) as daily_conversions,
                AVG(trial_conversion_rate) as daily_conversion_rate
            FROM js_app_subscription_daily_stats
            ${whereClause}
            GROUP BY stat_date
            ORDER BY stat_date ASC
            LIMIT 30
        `;
        const trendResult = await mysqlQuery(trendSql, params);

        res.json({
            success: true,
            data: {
                summary: summaryResult[0],
                trend: trendResult
            }
        });

    } catch (error) {
        console.error('获取统计概览失败:', error);
        res.status(500).json({
            success: false,
            message: '获取统计概览失败',
            error: error.message
        });
    }
});

/**
 * 手动执行统计任务（POST）
 */
router.post('/execute-stats', async (req, res) => {
    try {
        const { pk, date } = req.body;

        if (!pk) {
            return res.status(400).json({
                success: false,
                message: '包名不能为空'
            });
        }

        // TODO: 调用实际统计任务逻辑
        res.json({
            success: true,
            message: '统计任务执行成功'
        });

    } catch (error) {
        console.error('执行统计任务失败:', error);
        res.status(500).json({
            success: false,
            message: '执行统计任务失败',
            error: error.message
        });
    }
});

/**
 * 执行历史数据统计（POST）
 */
router.post('/execute-historical', async (req, res) => {
    try {
        const { pk, start_date, end_date } = req.body;

        if (!pk || !start_date || !end_date) {
            return res.status(400).json({
                success: false,
                message: '包名、开始日期和结束日期不能为空'
            });
        }

        // TODO: 调用实际历史数据统计逻辑

        res.json({
            success: true,
            message: '历史数据统计任务已开始，请稍后查看结果'
        });

    } catch (error) {
        console.error('启动历史数据统计失败:', error);
        res.status(500).json({
            success: false,
            message: '启动历史数据统计失败',
            error: error.message
        });
    }
});

/**
 * 获取任务状态（POST）
 */
router.post('/job-status', async (req, res) => {
    try {
        // 这里应该返回实际的任务状态
        const status = {
            isRunning: true,
            lastExecuteTime: new Date().toISOString(),
            nextExecuteTime: new Date(Date.now() + 24 * 60 * 60 * 1000).toISOString(),
            packages: ['com.example.app1', 'com.example.app2']
        };

        res.json({
            success: true,
            data: status
        });
    } catch (error) {
        console.error('获取任务状态失败:', error);
        res.status(500).json({
            success: false,
            message: '获取任务状态失败',
            error: error.message
        });
    }
});

/**
 * 测试接口（POST）
 */
router.post('/test', async (req, res) => {
    try {
        const testSql = 'SELECT COUNT(*) as count FROM js_app_subscription_daily_stats';
        const result = await mysqlQuery(testSql);

        res.json({
            success: true,
            message: '测试成功',
            data: result[0]
        });
    } catch (error) {
        console.error('测试失败:', error);
        res.status(500).json({
            success: false,
            message: '测试失败',
            error: error.message
        });
    }
});



router.post('/statisticOverview', async (req, res) => {
    // 1. 获取参数
    const { pk, product_id, start_date, end_date } = req.body;
    const sqlWhere = []
    const vals = [];
    if (pk) {
        sqlWhere.push('pk = ?');
        vals.push(pk);
    }
    if (product_id) {
        sqlWhere.push('product_id = ?');
        vals.push(product_id);
    }
    if (start_date) {
        sqlWhere.push('stat_date >= ?');
        vals.push(start_date);
    }
    if (end_date) {
        sqlWhere.push('stat_date <= ?');
        vals.push(end_date);
    }
    let whereStr = sqlWhere.length > 0 ? 'WHERE ' + sqlWhere.join(' AND ') : '';

    // 2. 汇总SQL
//     const summarySql = `
//     SELECT 
//         SUM(successful_renewals) AS total_renewals,
//         SUM(successful_refunds) AS total_refunds,
//         SUM(trial_users) AS total_trial_users,
//         SUM(one_time_purchase_users) AS total_one_time_purchase,
//         SUM(direct_purchase_users) AS total_direct_purchase,
//         SUM(trial_to_paid_count) AS total_conversions,
//         IFNULL(
//           AVG(
//             CASE
//               WHEN trial_to_paid_count > 0 AND trial_users > 0
//               THEN trial_to_paid_count / trial_users
//               ELSE NULL
//             END
//           )
//         , 0) AS avg_conversion_rate
//     FROM js_app_subscription_daily_stats
//     ${whereStr}
// `;
    const summarySql = `
    SELECT 
        SUM(successful_renewals) AS total_renewals,
        SUM(successful_refunds) AS total_refunds,
        SUM(trial_users) AS total_trial_users,
        SUM(one_time_purchase_users) AS total_one_time_purchase,
        SUM(direct_purchase_users) AS total_direct_purchase,
        SUM(trial_to_paid_count) AS total_conversions,
        SUM(resubscribe_count) AS total_resubscribe,
        IFNULL(
          AVG(
            CASE
              WHEN  trial_users > 0
              THEN trial_to_paid_count / trial_users
              ELSE NULL
            END
          )
        , 0) AS avg_conversion_rate
    FROM js_app_subscription_daily_stats
    ${whereStr}
`;
    // 3. 趋势SQL（按日期聚合）
    // 这里直接 group by stat_date 得到按天的趋势
    const trendSql = `
    SELECT 
        stat_date,
        SUM(successful_renewals) AS daily_renewals,
        SUM(successful_refunds) AS daily_refunds,
        SUM(trial_users) AS daily_trial_users,
        SUM(one_time_purchase_users) AS daily_one_time_purchase,
        SUM(direct_purchase_users) AS daily_direct_purchase,
        SUM(trial_to_paid_count) AS daily_conversions,
        SUM(resubscribe_count) AS daily_resubscribe,
        IFNULL(SUM(trial_to_paid_count) / NULLIF(SUM(trial_users), 0), 0) AS daily_conversion_rate
    FROM js_app_subscription_daily_stats
    ${whereStr}
    GROUP BY stat_date
    ORDER BY stat_date ASC
`;

    try {
        const [summaryRes, trendRes] = await Promise.all([
            mysqlQuery(summarySql, vals),
            mysqlQuery(trendSql, vals)
        ]);
        // 新增：将trendRes的stat_date转为北京时间字符串（yyyy-MM-dd）
        trendRes.forEach(r => {
            if (r.stat_date) {
                // 1. 转moment对象 2. 转东八区 3. 格式化为"YYYY-MM-DD"
                r.stat_date = moment(r.stat_date).tz('Asia/Shanghai').format('YYYY-MM-DD');
            }
        });

        res.json({
            success: true,
            data: {
                summary: summaryRes[0] || {},
                trend: trendRes || []
            }
        });
    } catch (e) {
        res.json({ success: false, msg: e.message || '查询失败' });
    }
});
router.post('/statisticOverviewMOMO', async (req, res) => {
    // 1. 获取参数
    const { pk, product_id, start_date, end_date } = req.body;
    const sqlWhere = []
    const vals = [];
    if (pk) {
        sqlWhere.push('pk = ?');
        vals.push(pk);
    }
    if (product_id) {
        sqlWhere.push('product_id = ?');
        vals.push(product_id);
    }
    if (start_date) {
        sqlWhere.push('stat_date >= ?');
        vals.push(start_date);
    }
    if (end_date) {
        sqlWhere.push('stat_date <= ?');
        vals.push(end_date);
    }
    let whereStr = sqlWhere.length > 0 ? 'WHERE ' + sqlWhere.join(' AND ') : '';

    // 2. 汇总SQL
    //     const summarySql = `
    //     SELECT 
    //         SUM(successful_renewals) AS total_renewals,
    //         SUM(successful_refunds) AS total_refunds,
    //         SUM(trial_users) AS total_trial_users,
    //         SUM(one_time_purchase_users) AS total_one_time_purchase,
    //         SUM(direct_purchase_users) AS total_direct_purchase,
    //         SUM(trial_to_paid_count) AS total_conversions,
    //         IFNULL(
    //           AVG(
    //             CASE
    //               WHEN trial_to_paid_count > 0 AND trial_users > 0
    //               THEN trial_to_paid_count / trial_users
    //               ELSE NULL
    //             END
    //           )
    //         , 0) AS avg_conversion_rate
    //     FROM js_app_subscription_daily_stats
    //     ${whereStr}
    // `;
    const summarySql = `
    SELECT 
        SUM(successful_renewals) AS total_renewals,
        SUM(successful_refunds) AS total_refunds,
        SUM(trial_users) AS total_trial_users,
        SUM(one_time_purchase_users) AS total_one_time_purchase,
        SUM(direct_purchase_users) AS total_direct_purchase,
        SUM(trial_to_paid_count) AS total_conversions,
        SUM(resubscribe_count) AS total_resubscribe,
        IFNULL(
          AVG(
            CASE
              WHEN  trial_users > 0
              THEN trial_to_paid_count / trial_users
              ELSE NULL
            END
          )
        , 0) AS avg_conversion_rate
    FROM js_app_subscription_daily_stats
    ${whereStr}
`;
    // 3. 趋势SQL（按日期聚合）
    // 这里直接 group by stat_date 得到按天的趋势
    const trendSql = `
    SELECT 
        stat_date,
        SUM(successful_renewals) AS daily_renewals,
        SUM(successful_refunds) AS daily_refunds,
        SUM(trial_users) AS daily_trial_users,
        SUM(one_time_purchase_users) AS daily_one_time_purchase,
        SUM(direct_purchase_users) AS daily_direct_purchase,
        SUM(trial_to_paid_count) AS daily_conversions,
        SUM(resubscribe_count) AS daily_resubscribe,
        IFNULL(SUM(trial_to_paid_count) / NULLIF(SUM(trial_users), 0), 0) AS daily_conversion_rate
    FROM js_app_subscription_daily_stats
    ${whereStr}
    GROUP BY stat_date
    ORDER BY stat_date ASC
`;

    try {
        const [summaryRes, trendRes] = await Promise.all([
            mysqlQueryMOMO(summarySql, vals),
            mysqlQueryMOMO(trendSql, vals)
        ]);
        // 新增：将trendRes的stat_date转为北京时间字符串（yyyy-MM-dd）
        trendRes.forEach(r => {
            if (r.stat_date) {
                // 1. 转moment对象 2. 转东八区 3. 格式化为"YYYY-MM-DD"
                r.stat_date = moment(r.stat_date).tz('Asia/Shanghai').format('YYYY-MM-DD');
            }
        });

        res.json({
            success: true,
            data: {
                summary: summaryRes[0] || {},
                trend: trendRes || []
            }
        });
    } catch (e) {
        res.json({ success: false, msg: e.message || '查询失败' });
    }
});
module.exports = router;